"""
    数据库的连接以及生命周期管理均在服务层(SQLServiceMgr)中设定,本层只负责实现基本功能
"""
from sqlalchemy import select, insert, update, delete,case,text,distinct
from .tables import *
from sqlalchemy import func, and_, or_, not_
from datetime import datetime, timedelta
from pytz import timezone
import pandas as pd

def update_data(conn, table,condition,value_map:dict):
    """
        更新数据库数据(一般用不到,只可能会更改标的名称)
    """
    stmt = update(table).values(value_map).where(condition)
    conn.execute(stmt)

def delete_data(conn, table, condition):
    """
        删除数据
    """
    conn.execute(table.delete().where(condition))

def check(conn,table,symbol)->bool:
    """
        检查某些表是否存在某些标的数据
    """
    res = conn.execute(select(table).where(table.c.symbol==symbol))
    return res.fetchone() is None

def get_symbol_ranges(conn, symbol: str, table_type: str) -> list:
    """获取指定标的的时间跨度"""
    if table_type not in ('min', 'tick'):
        raise ValueError(f"无效数据类型: {table_type}，仅支持'min'或'tick'")
    range_table = symbol_min_range if table_type == 'min' else symbol_tick_range
    query = select(range_table).where(range_table.c.symbol == symbol)
    result = conn.execute(query)
    return [(row.start_time, row.end_time) for row in result]

def get_symbols(conn,table_type:str)->list:
    """获取指定表中的标的编码"""
    if table_type not in ('min', 'tick'):
        raise ValueError(f"无效数据类型: {table_type}，仅支持'min'或'tick'")
    table = symbol_min_range if table_type == 'min' else symbol_tick_range
    stmt = select(distinct(table.c.symbol)).select_from(table)
    res = conn.execute(stmt)
    return [row[0] for row in res] if res else []

def get_data(conn, symbols: list, start_time: datetime, end_time: datetime, frequency,
            fields=None, filter_st=False) -> pd.DataFrame:
    # 确定数据源表
    if frequency == 'tick':
        table = symbol_tick
        time_col = symbol_tick.c.created_at
        # 有效字段：tick表所有列
        allowed_fields = set(table.columns.keys())
    else:
        table = symbol_min
        time_col = symbol_min.c.time
        # 有效字段：基础K线字段（聚合和非聚合共用）
        allowed_fields = {'symbol', 'time', 'open', 'close', 'high', 'low', 'volume', 'amount'}

    # 处理字段选择逻辑
    if fields:
        # 过滤无效字段并转为集合提高效率
        valid_fields = set(fields) & allowed_fields
    else:
        # 默认选择所有有效字段
        valid_fields = allowed_fields
    
    # ST过滤子查询（提前定义，避免重复）
    st_subq = None
    if filter_st:
        st_subq = select([symbol_name.c.symbol]).where(
            symbol_name.c.symbol_name.like('%ST%')
        ).scalar_subquery()

    # 基础查询条件（所有频率共用）
    conditions = [
        table.c.symbol.in_(symbols),  # 改为selectin_load() 如果symbols量大
        time_col.between(start_time, end_time)
    ]
    if filter_st:
        conditions.append(table.c.symbol.not_in(st_subq))

    # 处理不同频率的查询
    if frequency in ['tick', 'min']:
        # 直接查询原始表 ------
        # 选择字段（显式指定列避免返回id等无用字段）
        if valid_fields:
            cols = [table.c[f] for f in valid_fields]
        else:
            cols = [table]
        
        query = select(*cols).where(and_(*conditions))
        
    else:
        # 聚合查询（日/周/月/年） ------
        # 1. 生成时间分桶表达式
        time_bucket = _get_time_bucket_expr(time_col, frequency)
        
        # 2. 构建子查询字段（只包含必需字段）
        subq_fields = [
            table.c.symbol,
            time_bucket.label('bucket_time'),
            func.row_number().over(
                partition_by=[table.c.symbol, time_bucket],
                order_by=time_col.asc()
            ).label('asc_rn'),
            func.row_number().over(
                partition_by=[table.c.symbol, time_bucket],
                order_by=time_col.desc()
            ).label('desc_rn')
        ]
        
        # 添加用户请求的原始字段（确保聚合计算可用）
        for field in valid_fields:
            if field not in {'symbol', 'time'} and hasattr(table.c, field):
                subq_fields.append(table.c[field])
        
        # 3. 构建子查询
        subq = select(*subq_fields).where(
            and_(*conditions)
        ).subquery()
        
        # 4. 动态构建聚合表达式
        agg_fields = [
            subq.c.symbol,
            subq.c.bucket_time.label('time')  # 统一输出字段名为time
        ]
        
        # 按需添加聚合字段
        if 'open' in valid_fields:
            agg_fields.append(
                func.max(case((subq.c.asc_rn == 1, subq.c.open))).label('open')
            )
        if 'close' in valid_fields:
            agg_fields.append(
                func.max(case((subq.c.desc_rn == 1, subq.c.close))).label('close')
            )
        if 'high' in valid_fields:
            agg_fields.append(func.max(subq.c.high).label('high'))
        if 'low' in valid_fields:
            agg_fields.append(func.min(subq.c.low).label('low'))
        if 'volume' in valid_fields:
            agg_fields.append(func.sum(subq.c.volume).label('volume'))
        if 'amount' in valid_fields:
            agg_fields.append(func.sum(subq.c.amount).label('amount'))
        
        # 5. 主查询
        query = select(*agg_fields).group_by(
            subq.c.symbol, subq.c.bucket_time
        )

    # 添加执行注释（优化器提示）
    if frequency == 'tick':
        query = query.prefix_with("/*+ INDEX(symbol_tick idx_unique_symbol_created_at) */")
    elif frequency == 'min':
        query = query.prefix_with("/*+ INDEX(symbol_min idx_unique_symbol_time) */")
        
    return pd.read_sql(query, conn)

def _get_time_bucket_expr(time_col, frequency):
    """MySQL兼容的时间分桶表达式生成器"""
    if frequency == 'day':
        return func.date(time_col)
    elif frequency == 'week':
        # MySQL的WEEK函数默认从周日开始，需要调整
        return func.date_sub(
            func.date(time_col),
            text(f"INTERVAL (DAYOFWEEK({time_col.key})-1) DAY")
        )
    elif frequency == 'month':
        return func.date_format(time_col, '%Y-%m-01')
    elif frequency == 'year':
        return func.date_format(time_col, '%Y-01-01')
    else:
        raise ValueError(f"Unsupported frequency: {frequency}")
    
def delete_backtest(conn, backtest_name: str):
    """删除回测结果(含外键约束处理)"""
    # 临时禁用外键检查
    conn.execute("SET FOREIGN_KEY_CHECKS = 0")
    
    try:
        # 先删除交易记录(子表)
        conn.execute(
            backtest_trades.delete().where(
                backtest_trades.c.backtest_name == backtest_name
            )
        )
        # 再删除每日数据(子表)
        conn.execute(
            backtest_daily.delete().where(
                backtest_daily.c.backtest_name == backtest_name
            )
        )
        # 最后删除摘要(主表)
        conn.execute(
            backtest_summary.delete().where(
                backtest_summary.c.name == backtest_name
            )
        )
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise
    finally:
        # 恢复外键检查
        conn.execute("SET FOREIGN_KEY_CHECKS = 1")

def get_backtest_list(conn) -> list:
    """获取所有回测的名称列表"""
    stmt = select(backtest_summary.c.name, backtest_summary.c.start_time, backtest_summary.c.end_time)
    res = conn.execute(stmt)
    return [{"name": row[0], "start_time": row[1], "end_time": row[2]} for row in res]